package br.com.localfloripa.dao;

import java.util.ArrayList;
import java.util.List;

import javax.inject.Inject;
import javax.inject.Named;
import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;

import br.com.localfloripa.entities.Cliente;
import br.com.localfloripa.entities.Estado;
import br.com.localfloripa.entities.Municipio;

@Named("municipioDao")
@Scope("singleton")
public class MunicipioDao extends DaoManager<Municipio> {
	
	private static final Logger log = LoggerFactory.getLogger(MunicipioDao.class);
	
	@Inject
	@Named("representanteDao")
	private RepresentanteDao representanteDao;
	
	public Municipio findById(Long idMunicipio) throws Exception {
		return em.find(Municipio.class, idMunicipio);
	}
	
	public List<Municipio> listMunicipiosPorUFOrdemNome(Estado uf) throws Exception {
		
		Query query = em.createQuery("SELECT m FROM Municipio m WHERE m.estado.id = :estadoId ORDER BY m.nome");
		query.setParameter("estadoId", uf.getId());
		
		return (List<Municipio>) query.getResultList();		
	}
	
	public List<Municipio> listMunicipiosPorUFComAnunciosOrdemNome(Estado uf) throws Exception {
		
		Query query = em.createQuery("SELECT DISTINCT(m) FROM Municipio m JOIN m.enderecos e JOIN e.empresas emp JOIN emp.anuncio a WHERE m.estado.id = :estadoId ORDER BY m.nome");
		query.setParameter("estadoId", uf.getId());
		
		return (List<Municipio>) query.getResultList();		
	}
	
	public List<Municipio> maxMunicipiosByAnuncio(Estado estado) throws Exception {
		
		Query query = em.createQuery("SELECT m,COUNT(m.id) FROM Municipio m JOIN m.enderecos as e " +
				"JOIN e.empresas as emp JOIN emp.anuncio a JOIN a.cliente c " +
				"WHERE m.estado.id = :idEstado AND a.ativo IS TRUE AND c.ativo IS TRUE GROUP BY m.id,m.nome,m.estado ORDER BY COUNT(m.id) DESC");
		query.setParameter("idEstado", estado.getId());
		query.setMaxResults(5);	
		List<Municipio> municipios = new ArrayList<Municipio>();
		List<Object> objects = query.getResultList();
		if (objects != null) {
			for (int i = 0; i < objects.size(); i++) {
				Object[] arrayObject = (Object[]) objects.get(i);
				municipios.add((Municipio)arrayObject[0]);			
			}
		}						
		return municipios;
	}
	
	public List<String> maxBairrosByAnuncio(Estado estado) throws Exception {
		
		Query query = em.createQuery("SELECT DISTINCT(TRIM(e.bairro)),COUNT(e.id) FROM Endereco e JOIN e.empresas as emp JOIN emp.anuncio a JOIN a.cliente c " +
				"WHERE e.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND c.ativo IS TRUE GROUP BY e.bairro ORDER BY COUNT(e.id) DESC");
		query.setParameter("idEstado", estado.getId());
		query.setMaxResults(5);	
		List<String> bairros = new ArrayList<String>();
		List objects = query.getResultList();
		if (objects != null) {
			for (int i = 0; i < objects.size(); i++) {
				Object[] arrayObject = (Object[]) objects.get(i);
				bairros.add((String)arrayObject[0]);			
			}
		}						
		return bairros;
	}
	
	public List<String> listBairrosByMunicipioAndCliente(Municipio municipio,Cliente cliente) throws Exception {
		
		String jpql = null;
		if (representanteDao.findById(cliente.getId()) != null) { //corretor
			jpql = "SELECT DISTINCT(TRIM(UPPER(e.bairro))) FROM Endereco e JOIN e.empresas as emp JOIN emp.anuncio a JOIN a.representante r " +
					"WHERE e.municipio.id = :idMunicipio AND a.ativo IS TRUE AND r.ativo IS TRUE AND r.id = :idCliente AND a.ramo.id = 4 ";
		} else {
			jpql = "SELECT DISTINCT(TRIM(UPPER(e.bairro))) FROM Endereco e JOIN e.empresas as emp JOIN emp.anuncio a JOIN a.cliente c " +
					"WHERE e.municipio.id = :idMunicipio AND a.ativo IS TRUE AND c.ativo IS TRUE AND c.id = :idCliente AND a.ramo.id = 4 ";
		}		
		Query query = em.createQuery(jpql);
		query.setParameter("idMunicipio", municipio.getId());
		query.setParameter("idCliente", cliente.getId());
		List<String> bairros = new ArrayList<String>();
		List objects = query.getResultList();
		if (objects != null) {
			for (int i = 0; i < objects.size(); i++) {
				bairros.add((String)objects.get(i));			
			}
		}		
		return bairros;
	}
	
	public List<Municipio> listMunicipiosByEstadoAndCliente(Estado estado, Cliente cliente) throws Exception {
		
		String jpql = null;
		if (representanteDao.findById(cliente.getId()) != null) { //corretor
			jpql = "SELECT DISTINCT(m) FROM Municipio m JOIN m.enderecos as e JOIN e.empresas as emp JOIN emp.anuncio a JOIN a.representante r " +
					"WHERE e.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND r.ativo IS TRUE AND r.id = :idCliente AND a.ramo.id = 4 ORDER BY m.nome";
		} else {
			jpql = "SELECT DISTINCT(m) FROM Municipio m JOIN m.enderecos as e JOIN e.empresas as emp JOIN emp.anuncio a JOIN a.cliente c " +
					"WHERE e.municipio.estado.id = :idEstado AND a.ativo IS TRUE AND c.ativo IS TRUE AND c.id = :idCliente AND a.ramo.id = 4 ORDER BY m.nome";
		}		
		Query query = em.createQuery(jpql);
		query.setParameter("idEstado", estado.getId());
		query.setParameter("idCliente", cliente.getId());
		
		return (List<Municipio>) query.getResultList();		
	}
	
	public List<Estado> maxEstadosByAnuncio() throws Exception {
		
		Query query = em.createQuery("SELECT uf,COUNT(e.id) FROM Endereco e JOIN e.empresas as emp JOIN emp.anuncio a JOIN a.cliente c JOIN e.municipio.estado uf " +
		"WHERE a.ativo IS TRUE AND c.ativo IS TRUE GROUP BY uf.id,uf.nome,uf.sigla ORDER BY COUNT(e.id) DESC");
		query.setMaxResults(5);	
		List<Estado> estados = new ArrayList<Estado>();
		List objects = query.getResultList();
		if (objects != null) {
			for (int i = 0; i < objects.size(); i++) {
				Object[] arrayObject = (Object[]) objects.get(i);
				estados.add((Estado)arrayObject[0]);			
			}
		}						
		return estados;		
	}

}
